package com.synchrodata.util;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

public class DataBaseUtil {

	private static Logger logger = Logger.getLogger(DataBaseUtil.class);

    private static ComboPooledDataSource comboPooledDataSourceOne = null;
    private static ComboPooledDataSource comboPooledDataSourceTwo = null;

    //静态代码块
    static{
        //读取c3p0的xml配置文件创建数据源，c3p0的xml配置文件c3p0-config.xml必须放在src目录下
    	comboPooledDataSourceOne = new ComboPooledDataSource("one");
    	comboPooledDataSourceTwo = new ComboPooledDataSource("two");
    }

    //从数据源中获取数据库的连接
    public static Connection getOneConnection(){
    	try{
    		return comboPooledDataSourceOne.getConnection();
    	}catch(SQLException e){
    		logger.error("获取数据库连接异常："+e.getMessage());
    		 e.printStackTrace();
    		return null;
    	}

    }

    //从数据源中获取数据库的连接
    public static Connection getTwoConnection(){
    	try{
    		return comboPooledDataSourceTwo.getConnection();
    	}catch(SQLException e){
    		logger.error("获取数据库连接异常："+e.getMessage());
    		 e.printStackTrace();
    		return null;
    	}
    }

    /**
     * 执行sql 返回受影响的行
     * @param conn
     * @param sql
     * @return
     */
    public static int execute(Connection conn,String sql){
    	Statement st=null;
        try {
        	//创建事务,开启手动提交
			conn.setAutoCommit(false);
        	st = conn.createStatement();
        	int row = st.executeUpdate(sql);
        	//提交事务
			conn.commit();
            return row;
        } catch (SQLException e) {
        	//回滚事务
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
        	logger.error("执行sql出错："+e.getMessage());
            e.printStackTrace();
            return 0;
        }finally{
        	//释放资源
        	close(conn,st,null,null);
        }
    }

    /**
     * 执行sql 返回受影响的行
     * @param conn
     * @param sql
     * @param paras
     * @return
     */
    public static int execute(Connection conn,String sql,List<Object> paras){
    	PreparedStatement ps=null;
        try {
        	//创建事务,开启手动提交
			conn.setAutoCommit(false);
        	ps = conn.prepareStatement(sql);
            if(paras!=null && paras.size()>0){
            	int i=1;
            	for(Object paraObj:paras){
            		ps.setObject(i, paraObj);
            		i++;
            	}
            }
            int row = ps.executeUpdate();
            //提交事务
			conn.commit();
            return row;
        } catch (SQLException e) {
        	//回滚事务
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
        	logger.error("执行sql出错："+e.getMessage());
            e.printStackTrace();
            return 0;
        }finally{
        	//释放资源
        	close(conn,null,ps,null);
        }
    }

    /**
     * 执行查询语句
     * @param conn
     * @param sql
     * @param paras 参数集合
     * @return
     */
    public static List<Map<String, Object>> query(Connection conn,String sql,List<Object> paras){
        PreparedStatement ps=null;
        ResultSet rs = null;
        try {
        	ps = conn.prepareStatement(sql);
            if(paras!=null && paras.size()>0){
            	int i=1;
            	for(Object paraObj:paras){
            		ps.setObject(i, paraObj);
            		i++;
            	}
            }
            //建立一个结果集，用来保存查询出来的结果
            rs = ps.executeQuery();
            List<Map<String, Object>> list = fullToList(rs);
            return list;
        } catch (SQLException e) {
        	logger.error("查询sql出错："+e.getMessage());
            e.printStackTrace();
            return null;
        }finally{
        	//释放资源
        	close(conn,null,ps,rs);
        }
    }

    /**
     * 查询表的列字段
     * @param conn
     * @param tableName
     * @return
     */
	public static List<String> getColumnList(Connection conn, String tableName) {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "select lower(col.column_name) column_name from user_tab_columns col where lower(col.table_name)=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, tableName);
			// 建立一个结果集，用来保存查询出来的结果
			rs = ps.executeQuery();
			List<String> list = new ArrayList<String>();
			try {
				while (rs.next()) {
					list.add(rs.getString(1));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		} catch (SQLException e) {
			logger.error("查询sql出错：" + e.getMessage());
			e.printStackTrace();
			return null;
		} finally {
			// 释放资源
			close(conn, null, ps, rs);
		}
	}

	/**
	 * 批处理执行
	 * @param conn
	 * @param batchSql sql语句
	 */
	public static void sqlBatch(Connection conn,List<String> batchSql){
		//获取连接
		Statement st = null;
		try{
			if(batchSql!=null && batchSql.size()>0){
				//创建事务,开启手动提交
				conn.setAutoCommit(false);
				st = conn.createStatement();
				final int batchSize = 1000;
				int count = 0;
				for(String insertSql:batchSql){
					st.addBatch(insertSql);
					if(++count % batchSize == 0) {
						st.executeBatch();
						//提交事务
						conn.commit();
				    }
				}
				st.executeBatch();
				//提交事务
				conn.commit();
			}
		}catch(Exception e){
			//回滚事务
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			logger.error("批处理失败!错误原因: "+e.getMessage());
		}finally{
			close(conn,st,null,null);
		}
	}

	public static void updateData(Connection writeConn,String insertSql,List<String> colList,List<Map<String, Object>> insertDataList,
			Map<String, Object> recordMap,String pkCol,Connection readConn){

		if(insertDataList==null || insertDataList.isEmpty()){
			return;
		}

		for (Map<String, Object> map: insertDataList) {
			//更新主键值
			recordMap.put("table_pkid", map.get(pkCol));
			//将同步字段值更改为 Y
			map.put("is_sysc_data__", "Y");

			PreparedStatement upadtePs  = null;
			try{
				//创建事务,开启手动提交
				writeConn.setAutoCommit(false);
				//执行更新操作
				upadtePs  = writeConn.prepareStatement(insertSql);
				int i=1;
				for(String colName:colList){
					upadtePs.setObject(i, map.get(colName));
					i++;
				}
				upadtePs.executeUpdate();
				//提交事务
				writeConn.commit();

				//删除历史
				delRecord(readConn,recordMap);
			}catch(Exception e){
				try{
					//回滚之前的事务
					writeConn.rollback();

					String error = e.getMessage();
					if(error.contains("ORA-00001: 违反唯一约束条件")){
						//删除历史
						delRecord(readConn,recordMap);
					}else{
						if(error.length()>3900){
							error = error.substring(0, 3900);
						}
						//记录错误
						errorRecord(readConn,recordMap,error);
						e.printStackTrace();
						logger.error("更新数据失败1!错误原因: "+e.getMessage());
					}
				}catch(Exception e1){
					e1.printStackTrace();
					logger.error("更新数据失败2!错误原因: "+e1.getMessage());
				}
			}finally{
				close(writeConn,null,upadtePs,null);
			}
		}
	}

	/**
	 * 删除历史
	 * @param readConn
	 * @param recordMap
	 */
	public static void delRecord(Connection readConn,Map<String, Object> recordMap){
		//删除历史sql
		String delRecordSql = "delete from sys_record where table_name=? and table_pkid=? and record_state=? and record_time <=? ";
		PreparedStatement deletePs  = null;
		try{
			//创建事务,开启手动提交
			readConn.setAutoCommit(false);
			//执行删除历史操作
			deletePs  = readConn.prepareStatement(delRecordSql);
			deletePs.setObject(1, recordMap.get("table_name"));
			if(recordMap.get("table_name").toString().contains("rl_data_")){
				String pkid = convertStr(recordMap.get("table_pkid").toString());
				deletePs.setObject(2, pkid);
			}else{
				deletePs.setObject(2, recordMap.get("table_pkid"));
			}
			deletePs.setObject(3, recordMap.get("record_state"));
			deletePs.setObject(4, recordMap.get("record_time"));
			deletePs.executeUpdate();
			//提交事务
			readConn.commit();
		}catch(Exception e){
			//回滚之前的事务
			try {
				readConn.rollback();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
			logger.error("删除唯一数据: 失败！表名："+recordMap.get("table_name").toString()+"错误："+e.getMessage());
		}finally{
			close(readConn,null,deletePs,null);
		}
	}

	/**
	 * 记录错误
	 * @param readConn
	 * @param recordMap
	 * @param error
	 */
	public static void errorRecord(Connection readConn,Map<String, Object> recordMap,String error){
		//更新错误sql
	    String errorSql="update sys_record set record_error=? where table_name=? and table_pkid=? and record_state=? and record_time <=? ";
	    PreparedStatement errorPs= null;
		try{
			//创建事务,开启手动提交
			readConn.setAutoCommit(false);
			errorPs  = readConn.prepareStatement(errorSql);
			errorPs.setObject(1, error);
			errorPs.setObject(2, recordMap.get("table_name"));
			if(recordMap.get("table_name").toString().contains("rl_data_")){
				String pkid = convertStr(recordMap.get("table_pkid").toString());
				errorPs.setObject(3, pkid);
			}else{
				errorPs.setObject(3, recordMap.get("table_pkid"));
			}
			errorPs.setObject(4, recordMap.get("record_state"));
			errorPs.setObject(5, recordMap.get("record_time"));
			errorPs.executeUpdate();
			//提交事务
			readConn.commit();
		}catch(Exception e){
			//回滚之前的事务
			try {
				readConn.rollback();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
			logger.error("记录错误信息: 失败！表名："+recordMap.get("table_name").toString()+"错误："+e.getMessage());
		}finally{
			close(readConn,null,errorPs,null);
		}
	}


    /**
     * 释放资源，将数据库连接还给数据库连接池
     * @param conn
     * @param st
     * @param ps
     * @param rs
     */
	public static void close(Connection conn, Statement st,PreparedStatement ps, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (ps != null) {
						ps.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					try {
						/*if (conn != null) {
							conn.close();
						}*/
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	/**
	 * 关闭连接
	 * @param conns
	 */
	public static void close(Connection... conns) {
		try {
			if (conns!=null&&conns.length>0) {
				for (Connection conn : conns) {
					if (null != conn){
						conn.close();
					}
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

   /**
    * 把查询的结果转换成List
    * @param rs
    * @return list
    */
	public static List<Map<String, Object>> fullToList(ResultSet rs) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			int iColumn = rs.getMetaData().getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= iColumn; i++) {
					String tmpkey = rs.getMetaData().getColumnName(i);// 通过索引找到行的名字
					String key = tmpkey.toLowerCase(); // 转换为小写
					Object val = null;
					//switch行类型
					switch (rs.getMetaData().getColumnType(i)) {
						case Types.DATE:
							val = rs.getDate(i);
							break;
						case Types.TIMESTAMP:
							val = rs.getTimestamp(i);
							break;
						case Types.DOUBLE:
							if (rs.getObject(i) == null)
								val = null;
							else
								val = rs.getDouble(i);
							break;
						case Types.NUMERIC:
							if (rs.getObject(i) == null)
								val = null;
							else
								val = rs.getDouble(i);
							break;
						case Types.DECIMAL:
							if (rs.getObject(i) == null)
								val = null;
							else
								val = rs.getDouble(i);
							break;

						case Types.INTEGER:
							if (rs.getObject(i) == null)
								val = null;
							else{
								val = rs.getInt(i);
							}
							break;
						default:
							val = rs.getObject(i);
							break;
					}
					if (null != val) {
						// map找找不到指定键便设置个
						if (!map.containsKey(key)) {
							map.put(key, val);
						}
					}
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public static String convertStr(String str){
		BigDecimal bd = new BigDecimal(str);
		return bd.toPlainString();
	}
}

